﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using Excel = Microsoft.Office.Interop.Excel; 
namespace SMS
{
    public partial class fTraCuuSinhNhatKhachHang : Form
    {
        fLogin main;
        public fTraCuuSinhNhatKhachHang(fLogin main1)
        {
            InitializeComponent();
            main = main1;
        }

        int iRadio = 1;

        SqlConnection conn;
        SqlCommand command;
        SqlDataAdapter adapter;
        private void bntHuy_Click(object sender, EventArgs e)
        {
            this.Visible = false;
        }

        private void fTraCuuSinhNhatKhachHang_Load(object sender, EventArgs e)
        {
            bntExport.Enabled = false;
        }

        private void groupBox1_Enter(object sender, EventArgs e)
        {

        }

        private void bntTimKiem_Click(object sender, EventArgs e)
        {



            DataTable dtbResult = new DataTable();
            conn = new SqlConnection(main.ConnectDB);
            command = new SqlCommand();
            command.Connection = conn;


            if (iRadio == 1)
            {
                if (txtSongay.Text == null || txtSongay.Text == "")
                {
                    MessageBox.Show("Nhập số ngày!");
                }
                else
                {
                    DateTime BD = DateTime.Today;
                    DateTime KT = BD.AddDays(int.Parse(txtSongay.Text.ToString()));
                    //DateTime temp = birthday.AddYears(start.Year - birthday.Year);

                    command.CommandText = "SELECT HOTEN,SINHNHAT,DTDIDONG FROM KHACHHANG WHERE " +
                                           "(DATEADD(YEAR,YEAR(@BD)-YEAR(SINHNHAT)+1,SINHNHAT) > @BD AND " +
                                           "DATEADD(YEAR,YEAR(@BD)-YEAR(SINHNHAT)+1,SINHNHAT) < @KT)" +
                                           " OR (DATEADD(YEAR,YEAR(@BD)-YEAR(SINHNHAT),SINHNHAT) > @BD " +
                                            "AND DATEADD(YEAR,YEAR(@BD)-YEAR(SINHNHAT),SINHNHAT) < @KT)";
                    adapter = new SqlDataAdapter(command);

                    //command.Parameters.Add(new SqlParameter("@SONGAY", txtSongay.Text));
                    command.Parameters.Add(new SqlParameter("@BD", BD));
                    command.Parameters.Add(new SqlParameter("@KT", KT));



                    try
                    {
                        adapter.Fill(dtbResult);

                    }
                    catch (Exception ex)
                    {
                        string err = ex.Message;
                    }
                    dataGridView1.DataSource = dtbResult;
                    bntExport.Enabled = true;
                }

            }
            if (iRadio == 2)
            {

                if (cboNgay.Text == null || cboThang.Text == null)
                {
                    MessageBox.Show("Chọn ngày và tháng!");
                }
                else
                {
                    command.CommandText = "SELECT HOTEN,DTDIDONG,SINHNHAT From KHACHHANG where NGAYSINH =@ngay and THANGSINH = @thang";
                    adapter = new SqlDataAdapter(command);

                    command.Parameters.Add(new SqlParameter("@ngay", cboNgay.Text));
                    command.Parameters.Add(new SqlParameter("@thang", cboThang.Text));


                    try
                    {
                        adapter.Fill(dtbResult);

                    }
                    catch (Exception ex)
                    {
                        string err = ex.Message;
                    }
                    dataGridView1.DataSource = dtbResult;
                    bntExport.Enabled = true;
                }
            }

            
        }

        private void radioButton1_CheckedChanged(object sender, EventArgs e)
        {
            iRadio = 1;
            cboNgay.Text = null; 
            cboThang.Text = null; ;
        }

        private void radioButton2_CheckedChanged(object sender, EventArgs e)
        {
            iRadio = 2;
            txtSongay.ResetText();
        }

        private void txtType_KeyPress(object sender, KeyPressEventArgs e)
        {
            int isNumber = 0;
            e.Handled = !int.TryParse(e.KeyChar.ToString(), out isNumber);
        }

        private void bntExport_Click(object sender, EventArgs e)
        {
            Excel.Application xlApp ;
            Excel.Workbook xlWorkBook ;
            Excel.Worksheet xlWorkSheet ;
            object misValue = System.Reflection.Missing.Value;

            xlApp = new Excel.Application();
            xlWorkBook = xlApp.Workbooks.Add(misValue);
            xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1) ;
            int i = 0;
            int j = 0;

            for (i = 0; i <= dataGridView1.RowCount - 1; i++)
            {
                for (j = 0; j <= dataGridView1.ColumnCount - 1; j++)
                {
                    DataGridViewCell cell = dataGridView1[j, i];
                    xlWorkSheet.Cells[i + 1, j + 1] = cell.Value;
                }
            }

            xlWorkBook.SaveAs("D:\\sinhnhat.xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
            xlWorkBook.Close(true, misValue, misValue);
            xlApp.Quit();

            releaseObject(xlWorkSheet);
            releaseObject(xlWorkBook);
            releaseObject(xlApp);

            MessageBox.Show("Đã lưu thông tin ra file excel");
        }

        private void releaseObject(object obj)
        {
            try
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
                obj = null;
            }
            catch (Exception ex)
            {
                obj = null;
                MessageBox.Show("Exception Occured while releasing object " + ex.ToString());
            }
            finally
            {
                GC.Collect();
            }
        }

        private void txtSongay_TextChanged(object sender, EventArgs e)
        {

        }

        private void txtSongay_KeyPress(object sender, KeyPressEventArgs e)
        {
            if (!Char.IsDigit(e.KeyChar) && !Char.IsControl(e.KeyChar))
            {
                e.Handled = true;
            }
            else
            {
            }
        }

}
        }
       


